﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PrePurchaseRequestItemDAL : IDALPrePurchaseRequestItem
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PrePurchaseRequestItemDAL()
        {

        }

        public IList<PrePurchaseRequestItemInfo> GetPrePurchaseRequestItem()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT pi.*"
                            + ", s.BGM_SOURCE_NAME"
                            + ", x.BGM_EXP_NAME"
                            + ", i.ICM_RES_ITEM_NAME"
                            + ", u.ICM_UNIT_SEQ"
                            + ", u.ICM_UNIT_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM pi"
                            + " LEFT JOIN BGM_MAS_SOURCE s ON pi.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ"
                            + " LEFT JOIN BGM_MAS_EXPENSE x ON pi.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON pi.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE pi.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoSource.Seq = (Convert.IsDBNull(dr[i]["BGM_SOURCE_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_SOURCE_SEQ"]));
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["BGM_EXP_NAME"]).ToString();
                        infoItem.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_ITEM_SEQ"]));
                        infoItem.Name = (dr[i]["ICM_RES_ITEM_NAME"]).ToString();
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[i]["ICM_UNIT_NAME"]).ToString();
                        info.Name = (infoItem.Name.Length > 0 ? infoItem.Name : infoExpense.Name);
                        info.Price = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PPR_ITEM_PRICE"]));
                        info.Std_price = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_STD_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PPR_ITEM_STD_PRICE"]));
                        info.Qty = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_ITEM_QTY"]));
                        info.Descr = (dr[i]["ICM_PPR_ITEM_DESCR"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoExpense = infoExpense;
                        info.InfoSource = infoSource;
                        info.InfoItem = infoItem;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestItemInfo> GetBookMoney(int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_pr_seq", PrSeq);

                strSQL = "SELECT i.ICM_PPR_ITEM_SEQ"
                        + ", x.BGM_EXP_SEQ"
                        + ", x.BGM_EXP_NAME"
                        + ", CAST(concat(coalesce(r.icm_res_item_name, x.bgm_exp_name)"
                            + ", cast(' จำนวน ' as char)"
                            + "	, format("
                                + " coalesce((select coalesce(icm_pr_item_qty,0)" 
                                    +" from icm_purchase_request_item"
                                    + " where icm_pr_seq = @icm_pr_seq"
                                        + "	and case when icm_res_item_seq is not null"
                                            + " then icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 0)"
                            + "	, ' ', coalesce(u.icm_unit_name, 'รายการ')"
                            + "	, cast(' ในวงเงินจัดหา ' as char) "
                                + "	, format("
                                    + " coalesce((select(coalesce(icm_pr_item_qty,0) * coalesce(icm_pr_item_price,0))"
                                    + " from icm_purchase_request_item  "
                                    + " where icm_pr_seq = @icm_pr_seq  "
                                        + "	and case when icm_res_item_seq is not null"
                                            + " then icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 2)"
                            + "	, ' บาท') AS CHAR)"
                        + "	AS ITEM_DESCR"
                        + ", js.JOB_SITE_NAME"
                        + ", s.BGM_SOURCE_NAME"
                        + ", i.ICM_PPR_ITEM_QTY"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", i.ICM_PPR_ITEM_PRICE"
                        + ", COALESCE(i.ICM_PPR_ITEM_QTY, 0) * COALESCE(i.ICM_PPR_ITEM_PRICE, 0) AS SUM_AMOUNT"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM i"
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST ppr ON i.ICM_PPR_SEQ = ppr.ICM_PPR_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON i.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON ppr.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " INNER JOIN BGM_MAS_SOURCE s ON s.BGM_SOURCE_SEQ = i.BGM_SOURCE_SEQ" 
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM r ON r.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = r.ICM_UNIT_SEQ"
                        + " WHERE i.ICM_PR_ITEM_SEQ IN"
                            + "	(select icm_pr_item_seq"
                            + "	from icm_purchase_request_item"
                            + "	where icm_pr_seq = @icm_pr_seq)"
                            + " AND i.ICM_PR_ITEM_SEQ IS NOT NULL";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        PrePurchaseRequestInfo infoPpr = new PrePurchaseRequestInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["bgm_exp_name"]).ToString();
                        info.Descr = (dr[i]["item_descr"]).ToString();
                        infoJobSite.Name = (dr[i]["job_site_name"]).ToString();
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        info.Qty = Convert.ToInt32((dr[i]["icm_ppr_item_qty"].ToString()));
                        infoUnit.Name = (dr[i]["icm_unit_name"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["icm_ppr_item_price"].ToString());
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["SUM_AMOUNT"]));

                        info.InfoExpense = infoExpense;
                        infoPpr.InfoJobSite = infoJobSite;
                        info.InfoPpr = infoPpr;
                        info.InfoSource = infoSource;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestItemInfo> GetBookMoneyForBidder(int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_pr_seq", PrSeq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT i.ICM_PPR_ITEM_SEQ"
                        + ", x.BGM_EXP_SEQ"
                        + ", x.BGM_EXP_NAME"
                        + ", CAST(concat(coalesce(r.icm_res_item_name, x.bgm_exp_name)"
                            + ", cast(' จำนวน ' as char)"
                            + "	, format("
                                + " coalesce((select sum(coalesce(q.item_qty,0))"
                                    + " from icm_bidder_quotation q"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where ri.icm_pr_seq = @icm_pr_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 0)"
                            + "	, ' ', coalesce(u.icm_unit_name, 'รายการ')"
                            + "	, cast(' ในวงเงินจัดหา ' as char) "
                                + "	, format("
                                    + " coalesce((select sum(coalesce(q.net_amount,0))"
                                    + " from icm_bidder_quotation q"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where ri.icm_pr_seq = @icm_pr_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 2)"
                            + "	, ' บาท') AS CHAR)"
                        + "	AS ITEM_DESCR"
                        + ", js.JOB_SITE_NAME"
                        + ", s.BGM_SOURCE_NAME"
                        + ", i.ICM_PPR_ITEM_QTY"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", i.ICM_PPR_ITEM_PRICE"
                        + ", COALESCE(i.ICM_PPR_ITEM_QTY, 0) * COALESCE(i.ICM_PPR_ITEM_PRICE, 0) AS SUM_AMOUNT"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM i"
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST ppr ON i.ICM_PPR_SEQ = ppr.ICM_PPR_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON i.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON ppr.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " INNER JOIN BGM_MAS_SOURCE s ON s.BGM_SOURCE_SEQ = i.BGM_SOURCE_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM r ON r.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = r.ICM_UNIT_SEQ"
                        + " WHERE i.ICM_PR_ITEM_SEQ IN"
                            + "	(select icm_pr_item_seq"
                            + "	from icm_purchase_request_item"
                            + "	where icm_pr_seq = @icm_pr_seq)"
                            + " AND i.ICM_PR_ITEM_SEQ IS NOT NULL";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        PrePurchaseRequestInfo infoPpr = new PrePurchaseRequestInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["bgm_exp_name"]).ToString();
                        info.Descr = (dr[i]["item_descr"]).ToString();
                        infoJobSite.Name = (dr[i]["job_site_name"]).ToString();
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        info.Qty = Convert.ToInt32((dr[i]["icm_ppr_item_qty"].ToString()));
                        infoUnit.Name = (dr[i]["icm_unit_name"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["icm_ppr_item_price"].ToString());
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["SUM_AMOUNT"]));

                        info.InfoExpense = infoExpense;
                        infoPpr.InfoJobSite = infoJobSite;
                        info.InfoPpr = infoPpr;
                        info.InfoSource = infoSource;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestItemInfo> GetBookMoneyForBid(int bid_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_bid_seq", bid_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT i.ICM_PPR_ITEM_SEQ"
                        + ", x.BGM_EXP_SEQ"
                        + ", x.BGM_EXP_NAME"
                        + ", CAST(concat(coalesce(r.icm_res_item_name, x.bgm_exp_name)"
                            + ", cast(' จำนวน ' as char)"
                            + "	, format("
                                + " coalesce((select sum(coalesce(q.item_qty,0))"
                                    + " from icm_bidder_quotation q"
                                        + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where b.icm_bid_seq = @icm_bid_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 0)"
                            + "	, ' ', coalesce(u.icm_unit_name, 'รายการ')"
                            + "	, cast(' ในวงเงินจัดหา ' as char) "
                                + "	, format("
                                    + " coalesce((select sum(coalesce(q.net_amount,0))"
                                    + " from icm_bidder_quotation q"
                                        + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where b.icm_bid_seq = @icm_bid_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 2)"
                            + "	, ' บาท') AS CHAR)"
                        + "	AS ITEM_DESCR"
                        + ", js.JOB_SITE_NAME"
                        + ", s.BGM_SOURCE_NAME"
                        + ", i.PR_ITEM_QTY"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", (i.PR_ITEM_AMOUNT/i.PR_ITEM_QTY) AS PR_ITEM_PRICE "
                        + ", COALESCE(i.PR_ITEM_AMOUNT, 0) AS SUM_AMOUNT"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM i"
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST ppr ON i.ICM_PPR_SEQ = ppr.ICM_PPR_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON i.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON ppr.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " INNER JOIN BGM_MAS_SOURCE s ON s.BGM_SOURCE_SEQ = i.BGM_SOURCE_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM r ON r.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = r.ICM_UNIT_SEQ"
                        + " WHERE i.ICM_PR_ITEM_SEQ IN"
                            + "	(select q.icm_pr_item_seq"
                            + " from icm_bidder_quotation q"
                                + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                            + "	where b.icm_bid_seq = @icm_bid_seq"
                                + " and q.selected = @yes_status)"
                            + " AND i.ICM_PR_ITEM_SEQ IS NOT NULL";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        PrePurchaseRequestInfo infoPpr = new PrePurchaseRequestInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["bgm_exp_name"]).ToString();
                        info.Descr = (dr[i]["item_descr"]).ToString();
                        infoJobSite.Name = (dr[i]["job_site_name"]).ToString();
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        info.Qty = Convert.ToInt32((dr[i]["PR_ITEM_QTY"].ToString()));
                        infoUnit.Name = (dr[i]["icm_unit_name"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["PR_ITEM_PRICE"].ToString());
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["SUM_AMOUNT"]));

                        info.InfoExpense = infoExpense;
                        infoPpr.InfoJobSite = infoJobSite;
                        info.InfoPpr = infoPpr;
                        info.InfoSource = infoSource;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestItemInfo> GetBookMoneyForPurchaseOrder(int po_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_po_seq", po_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT i.ICM_PPR_ITEM_SEQ"
                        + ", x.BGM_EXP_SEQ"
                        + ", x.BGM_EXP_NAME"
                        + ", CAST(concat(coalesce(r.icm_res_item_name, x.bgm_exp_name)"
                            + ", cast(' จำนวน ' as char)"
                            + "	, format("
                                + " coalesce((select sum(coalesce(ii.item_qty,0))"
                                    + " from icm_purchase_order_installment oi"
                                        + " inner join icm_purchase_order_installment_item ii on oi.icm_po_install_seq = ii.icm_po_install_seq"
                                        + " inner join icm_bidder_quotation q ON ii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                                        + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where oi.icm_po_seq = @icm_po_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 0)"
                            + "	, ' ', coalesce(u.icm_unit_name, 'รายการ')"
                            + "	, cast(' ในวงเงินจัดหา ' as char) "
                                + "	, format("
                                    + " coalesce((select sum(coalesce(ii.net_amount,0))"
                                    + " from icm_purchase_order_installment oi"
                                        + " inner join icm_purchase_order_installment_item ii on oi.icm_po_install_seq = ii.icm_po_install_seq"
                                        + " inner join icm_bidder_quotation q ON ii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                                        + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where oi.icm_po_seq = @icm_po_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                    + ", 0)"
                                + ", 2)"
                            + "	, ' บาท') AS CHAR)"
                        + "	AS ITEM_DESCR"
                        + ", js.JOB_SITE_NAME"
                        + ", s.BGM_SOURCE_NAME"
                        + ", COALESCE(i.BIDDER_QTY, 0) AS QTY"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", (COALESCE(i.BIDDER_AMOUNT, 0)/COALESCE(i.BIDDER_QTY, 1)) AS PRICE "
                        + ", COALESCE(i.BIDDER_AMOUNT, 0) AS SUM_AMOUNT"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM i"
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST ppr ON i.ICM_PPR_SEQ = ppr.ICM_PPR_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON i.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON ppr.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " INNER JOIN BGM_MAS_SOURCE s ON s.BGM_SOURCE_SEQ = i.BGM_SOURCE_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM r ON r.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = r.ICM_UNIT_SEQ"
                        + " WHERE i.ICM_PR_ITEM_SEQ IN"
                            + "	(select q.icm_pr_item_seq"
                            + " from icm_purchase_order_installment oi"
                                + " inner join icm_purchase_order_installment_item ii on oi.icm_po_install_seq = ii.icm_po_install_seq"
                                + " inner join icm_bidder_quotation q ON ii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                            + "	where oi.icm_po_seq = @icm_po_seq"
                                + " and q.selected = @yes_status)"
                            + " AND i.ICM_PR_ITEM_SEQ IS NOT NULL";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        PrePurchaseRequestInfo infoPpr = new PrePurchaseRequestInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["BGM_EXP_NAME"]).ToString();
                        info.Descr = (dr[i]["ITEM_DESCR"]).ToString();
                        infoJobSite.Name = (dr[i]["JOB_SITE_NAME"]).ToString();
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        info.Qty = Convert.ToInt32((dr[i]["QTY"].ToString()));
                        infoUnit.Name = (dr[i]["ICM_UNIT_NAME"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["PRICE"].ToString());
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["SUM_AMOUNT"]));

                        info.InfoExpense = infoExpense;
                        infoPpr.InfoJobSite = infoJobSite;
                        info.InfoPpr = infoPpr;
                        info.InfoSource = infoSource;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestItemInfo> GetBookMoneyForDeliveryNote(int dn_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_dn_seq", dn_seq);
                dbManager.AddParameters(1, "@yes_status", SystemInfo._YesStatus);

                strSQL = "SELECT i.ICM_PPR_ITEM_SEQ"
                        + ", x.BGM_EXP_SEQ"
                        + ", x.BGM_EXP_NAME"
                        + ", CAST(concat(coalesce(r.icm_res_item_name, x.bgm_exp_name)"
                            + ", cast(' จำนวน 'as char)"
                            + "	, format("
                                + " coalesce((select sum(coalesce(dni.item_qty,0))"
                                    + " from icm_delivery_note_item dni"
                                        + " inner join icm_purchase_order_installment_item ii on dni.icm_po_install_item_seq = ii.icm_po_install_item_seq"
                                        + " inner join icm_bidder_quotation q ON ii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                                        + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where dni.icm_dn_seq = @icm_dn_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                + ", 0)"
                            + ", 0)"
                            + "	, ' ', coalesce(u.icm_unit_name, 'รายการ')"
                            + "	, cast(' ในวงเงินจัดหา 'as char) "
                                + "	, format("
                                    + " coalesce((select sum(coalesce(dni.net_amount,0))"
                                    + " from icm_delivery_note_item dni"
                                        + " inner join icm_purchase_order_installment_item ii on dni.icm_po_install_item_seq = ii.icm_po_install_item_seq"
                                        + " inner join icm_bidder_quotation q ON ii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                                        + " inner join icm_bidder b on q.icm_bidder_seq = b.icm_bidder_seq"
                                        + " inner join icm_purchase_request_item ri on q.icm_pr_item_seq = ri.icm_pr_item_seq"
                                    + " where dni.icm_dn_seq = @icm_dn_seq"
                                        + " and q.selected = @yes_status"
                                        + "	and case when ri.icm_res_item_seq is not null"
                                            + " then ri.icm_res_item_seq = i.icm_res_item_seq"
                                            + "	else ri.bgm_exp_seq = i.bgm_exp_seq end)"
                                    + ", 0)"
                                + ", 2)"
                            + "	, ' บาท') AS CHAR)"
                        + "	AS ITEM_DESCR"
                        + ", js.JOB_SITE_NAME"
                        + ", s.BGM_SOURCE_NAME"
                        + ", COALESCE(i.PO_QTY, 0) AS QTY"
                        + ", COALESCE(u.ICM_UNIT_NAME, 'รายการ') AS ICM_UNIT_NAME"
                        + ", (COALESCE(i.PO_AMOUNT, 0)/COALESCE(i.PO_QTY, 1)) AS PRICE "
                        + ", COALESCE(i.PO_AMOUNT, 0) AS SUM_AMOUNT"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM i"
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST ppr ON i.ICM_PPR_SEQ = ppr.ICM_PPR_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON i.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES js ON ppr.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " INNER JOIN BGM_MAS_SOURCE s ON s.BGM_SOURCE_SEQ = i.BGM_SOURCE_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM r ON r.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = r.ICM_UNIT_SEQ"
                        + " WHERE i.ICM_PR_ITEM_SEQ IN"
                            + "	(select q.icm_pr_item_seq"
                            + " from icm_delivery_note_item dni"
                                + " inner join icm_purchase_order_installment_item ii on dni.icm_po_install_item_seq = ii.icm_po_install_item_seq"
                                + " inner join icm_bidder_quotation q ON ii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                            + "	where dni.icm_dn_seq = @icm_dn_seq"
                                + " and q.selected = @yes_status)"
                            + " AND i.ICM_PR_ITEM_SEQ IS NOT NULL";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        PrePurchaseRequestInfo infoPpr = new PrePurchaseRequestInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["BGM_EXP_NAME"]).ToString();
                        info.Descr = (dr[i]["ITEM_DESCR"]).ToString();
                        infoJobSite.Name = (dr[i]["JOB_SITE_NAME"]).ToString();
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        info.Qty = Convert.ToInt32((dr[i]["QTY"].ToString()));
                        infoUnit.Name = (dr[i]["ICM_UNIT_NAME"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["PRICE"].ToString());
                        info.SumAmount = (Convert.IsDBNull(dr[i]["SUM_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["SUM_AMOUNT"]));

                        info.InfoExpense = infoExpense;
                        infoPpr.InfoJobSite = infoJobSite;
                        info.InfoPpr = infoPpr;
                        info.InfoSource = infoSource;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestItemInfo> GetPrePurchaseRequestItem(string record_status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@record_status", record_status);

                strSQL = "SELECT pi.*"
                            + ", s.BGM_SOURCE_NAME"
                            + ", x.BGM_EXP_NAME"
                            + ", i.ICM_RES_ITEM_NAME"
                            + ", u.ICM_UNIT_SEQ"
                            + ", u.ICM_UNIT_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM pi"
                            + " LEFT JOIN BGM_MAS_SOURCE s ON pi.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ"
                            + " LEFT JOIN BGM_MAS_EXPENSE x ON pi.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON pi.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE pi.RECORD_STATUS = @record_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoSource.Seq = (Convert.IsDBNull(dr[i]["BGM_SOURCE_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_SOURCE_SEQ"]));
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["BGM_EXP_NAME"]).ToString();
                        infoItem.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_ITEM_SEQ"]));
                        infoItem.Name = (dr[i]["ICM_RES_ITEM_NAME"]).ToString();
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[i]["ICM_UNIT_NAME"]).ToString();
                        info.Name = (infoItem.Name.Length > 0 ? infoItem.Name : infoExpense.Name);
                        info.Price = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PPR_ITEM_PRICE"]));
                        info.Std_price = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_STD_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PPR_ITEM_STD_PRICE"]));
                        info.Qty = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_ITEM_QTY"]));
                        info.Descr = (dr[i]["ICM_PPR_ITEM_DESCR"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoStatus;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoExpense = infoExpense;
                        info.InfoSource = infoSource;
                        info.InfoItem = infoItem;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public PrePurchaseRequestItemInfo GetPrePurchaseRequestItem(int seq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT pi.*"
                            + ", s.BGM_SOURCE_NAME"
                            + ", x.BGM_EXP_NAME"
                            + ", i.ICM_RES_ITEM_NAME"
                            + ", u.ICM_UNIT_SEQ"
                            + ", u.ICM_UNIT_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM pi"
                            + " LEFT JOIN BGM_MAS_SOURCE s ON pi.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ"
                            + " LEFT JOIN BGM_MAS_EXPENSE x ON pi.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON pi.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON i.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE pi.ICM_PPR_ITEM_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    BgmSourceInfo infoSource = new BgmSourceInfo();
                    BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                    ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                    MasterUnitInfo infoUnit = new MasterUnitInfo();
                    RecordInfo infoRecord = new RecordInfo();
                    SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                    info.Seq = Convert.ToInt32(dr[0]["ICM_PPR_ITEM_SEQ"]);
                    infoSource.Seq = (Convert.IsDBNull(dr[0]["BGM_SOURCE_SEQ"]) ? null : (Nullable<int>)(dr[0]["BGM_SOURCE_SEQ"]));
                    infoSource.Name = (dr[0]["BGM_SOURCE_NAME"]).ToString();
                    infoExpense.Seq = (Convert.IsDBNull(dr[0]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[0]["BGM_EXP_SEQ"]));
                    infoExpense.Name = (dr[0]["BGM_EXP_NAME"]).ToString();
                    infoItem.Seq = (Convert.IsDBNull(dr[0]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_RES_ITEM_SEQ"]));
                    infoItem.Name = (dr[0]["ICM_RES_ITEM_NAME"]).ToString();
                    infoUnit.Seq = (Convert.IsDBNull(dr[0]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_UNIT_SEQ"]));
                    infoUnit.Name = (dr[0]["ICM_UNIT_NAME"]).ToString();
                    info.Name = (infoItem.Name.Length > 0 ? infoItem.Name : infoExpense.Name);
                    info.Price = (Convert.IsDBNull(dr[0]["ICM_PPR_ITEM_PRICE"]) ? null : (Nullable<double>)(dr[0]["ICM_PPR_ITEM_PRICE"]));
                    info.Std_price = (Convert.IsDBNull(dr[0]["ICM_PPR_ITEM_STD_PRICE"]) ? null : (Nullable<double>)(dr[0]["ICM_PPR_ITEM_STD_PRICE"]));
                    info.Qty = (Convert.IsDBNull(dr[0]["ICM_PPR_ITEM_QTY"]) ? null : (Nullable<int>)(dr[0]["ICM_PPR_ITEM_QTY"]));
                    info.Descr = (dr[0]["ICM_PPR_ITEM_DESCR"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    infoRecord.InfoStatus = infoStatus;
                    infoItem.InfoUnit = infoUnit;
                    info.InfoExpense = infoExpense;
                    info.InfoSource = infoSource;
                    info.InfoItem = infoItem;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<PrePurchaseRequestItemInfo> GetPrePurchaseRequestItemWithParent(int ppr_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@ppr_seq", ppr_seq);

                strSQL = " SELECT r.ICM_PC_RULE_SEQ"
                            + ", r.ICM_PC_RULE_NAME"
                            + ", s.BGM_SOURCE_NAME"
                            + ", po.BGM_PROJ_OPER_SEQ"
                            + ", po.BGM_PROJ_OPER_NAME"
                            + ", p.BGM_PROJ_SEQ"
                            + ", p.BGM_PROJ_NAME"
                            + ", x.BGM_EXP_NAME"
                            + ", i.ICM_RES_ITEM_NAME"
                            + ", u.ICM_UNIT_SEQ"
                            + ", u.ICM_UNIT_NAME"
                            + ", pi.*"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM pi"
                            + " LEFT JOIN BGM_PROJECT_OPERATION_EXPENSE poe ON pi.BGM_PROJ_OPER_EXP_SEQ = poe.BGM_PROJ_OPER_EXP_SEQ"
                            + " LEFT JOIN BGM_PROJECT_OPERATION po ON poe.BGM_PROJ_OPER_SEQ = po.BGM_PROJ_OPER_SEQ"
                            + " LEFT JOIN BGM_PROJECT_EXPENSE pe ON pi.BGM_PROJ_EXP_SEQ = pe.BGM_PROJ_EXP_SEQ"
                            + " LEFT JOIN BGM_PROJECT p ON pe.BGM_PROJ_SEQ = p.BGM_PROJ_SEQ"
                            + " INNER JOIN BGM_MAS_SOURCE s ON pi.BGM_SOURCE_SEQ = s.BGM_SOURCE_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE x ON pi.BGM_EXP_SEQ = x.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_RULE r ON pi.ICM_PC_RULE_SEQ = r.ICM_PC_RULE_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM i ON pi.ICM_RES_ITEM_SEQ = i.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON u.ICM_UNIT_SEQ = i.ICM_UNIT_SEQ"
                        + " WHERE pi.ICM_PPR_SEQ = @ppr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        BgmSourceInfo infoSource = new BgmSourceInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        MasterPurchaseRuleInfo infoRule = new MasterPurchaseRuleInfo();
                        BgmProjectOperationExpenseInfo infoProOperExp = new BgmProjectOperationExpenseInfo();
                        BgmProjectOperationInfo infoOperation = new BgmProjectOperationInfo();
                        BgmProjectExpenseInfo infoProjectExp = new BgmProjectExpenseInfo();
                        BgmProjectInfo infoProject = new BgmProjectInfo();
                        PurchaseRequestItemInfo infoPrItem = new PurchaseRequestItemInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_ITEM_SEQ"]);
                        infoSource.Seq = (Convert.IsDBNull(dr[i]["BGM_SOURCE_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_SOURCE_SEQ"]));
                        infoSource.Name = (dr[i]["BGM_SOURCE_NAME"]).ToString();
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = (dr[i]["BGM_EXP_NAME"]).ToString();
                        infoItem.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_ITEM_SEQ"]));
                        infoItem.Name = (dr[i]["ICM_RES_ITEM_NAME"]).ToString();
                        infoUnit.Seq = (Convert.IsDBNull(dr[i]["ICM_UNIT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_UNIT_SEQ"]));
                        infoUnit.Name = (dr[i]["ICM_UNIT_NAME"]).ToString();
                        info.Name = (infoItem.Name.Length > 0 ? infoItem.Name : infoExpense.Name);
                        info.Price = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PPR_ITEM_PRICE"]));
                        info.Std_price = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_STD_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PPR_ITEM_STD_PRICE"]));
                        info.Qty = (Convert.IsDBNull(dr[i]["ICM_PPR_ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_ITEM_QTY"]));
                        info.Descr = (dr[i]["ICM_PPR_ITEM_DESCR"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoRule.Seq = (Convert.IsDBNull(dr[i]["ICM_PC_RULE_SEQ"]) ? null : (Nullable<Int32>)(dr[i]["ICM_PC_RULE_SEQ"]));
                        infoRule.Name = (Convert.IsDBNull(dr[i]["ICM_PC_RULE_NAME"]) ? "" : (dr[i]["ICM_PC_RULE_NAME"]).ToString());
                        infoOperation.Seq = (Convert.IsDBNull(dr[i]["BGM_PROJ_OPER_SEQ"]) ? null : (Nullable<Int32>)(dr[i]["BGM_PROJ_OPER_SEQ"]));
                        infoOperation.Name = (Convert.IsDBNull(dr[i]["BGM_PROJ_OPER_NAME"]) ? "" : (dr[i]["BGM_PROJ_OPER_NAME"]).ToString());
                        infoProject.Seq = (Convert.IsDBNull(dr[i]["BGM_PROJ_SEQ"]) ? null : (Nullable<Int32>)(dr[i]["BGM_PROJ_SEQ"]));
                        infoProject.Name = (Convert.IsDBNull(dr[i]["BGM_PROJ_NAME"]) ? "" : (dr[i]["BGM_PROJ_NAME"]).ToString());
                        infoPrItem.Seq = (Convert.IsDBNull(dr[i]["icm_pr_item_seq"]) ? null : (Nullable<int>)(dr[i]["icm_pr_item_seq"]));
                        infoProOperExp.InfoOperation = infoOperation;
                        infoProjectExp.InfoProject = infoProject;

                        infoItem.InfoUnit = infoUnit;
                        info.InfoExpense = infoExpense;
                        info.InfoSource = infoSource;
                        info.InfoItem = infoItem;
                        info.InfoRecord = infoRecord;
                        info.InfoRule = infoRule;
                        info.InfoOperationExpense = infoProOperExp;
                        info.InfoProjectExpense = infoProjectExp;
                        info.InfoPrItem = infoPrItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public string GetPrePurchaseRequestRule(int ppr_seq)
        {
            string strSQL;
            string strRule = "";
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@ppr_seq", ppr_seq);

                strSQL = "SELECT DISTINCT m.ICM_PC_RULE_SEQ"
                        + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM i"
                            + " LEFT JOIN ICM_MAS_PURCHASE_RULE_MAPPING m ON i.BGM_SOURCE_SEQ = m.BGM_SOURCE_SEQ"
                        + " WHERE i.ICM_PPR_SEQ = @ppr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        strRule = (dr[i]["ICM_PC_RULE_SEQ"]).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strRule;
        }

        public IList<PrePurchaseRequestItemInfo> GetCollectItemName()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestItemInfo> infoList = new List<PrePurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();

                strSQL = "SELECT a.ICM_RES_ITEM_SEQ"
                        + ", a.ICM_RES_ITEM_NAME"
                        + " FROM "
                            + " (select ri.icm_res_item_name"
                                + ", ri.icm_res_item_seq"
                            + " from icm_pre_purchase_request_item pri"
                                + " inner join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq"
                            + " where pri.icm_pr_item_seq is null"
                            + " ) a"
                        + " GROUP BY a.ICM_RES_ITEM_SEQ"
                        + " HAVING COUNT(a.ICM_RES_ITEM_SEQ) > 1";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestItemInfo info = new PrePurchaseRequestItemInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_RES_ITEM_SEQ"]);
                        info.Name = dr[i]["ICM_RES_ITEM_NAME"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(IList<PrePurchaseRequestItemInfo> infoList, int ppr_seq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(13);

                    dbManager.AddParameters(0, "@descr", infoList[i].Descr);
                    dbManager.AddParameters(1, "@qty", infoList[i].Qty);
                    dbManager.AddParameters(2, "@price", infoList[i].Price);
                    dbManager.AddParameters(3, "@std_price", infoList[i].Std_price);
                    dbManager.AddParameters(4, "@exp_seq", infoList[i].InfoExpense.Seq);
                    dbManager.AddParameters(5, "@source_seq", infoList[i].InfoSource.Seq);
                    dbManager.AddParameters(6, "@item_seq", infoList[i].InfoItem.Seq);
                    dbManager.AddParameters(7, "@ppr_seq", ppr_seq);
                    dbManager.AddParameters(8, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManager.AddParameters(9, "@created_when", infoList[i].InfoRecord.Created_when);
                    dbManager.AddParameters(10, "@rule_seq", infoList[i].InfoRule.Seq);
                    dbManager.AddParameters(11, "@projExp_seq", (infoList[i].InfoProjectExpense != null ? infoList[i].InfoProjectExpense.Seq : null));
                    dbManager.AddParameters(12, "@operExp_seq", (infoList[i].InfoOperationExpense != null ? infoList[i].InfoOperationExpense.Seq : null));

                    strSQL = "INSERT INTO ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " (ICM_PPR_ITEM_DESCR, ICM_PPR_ITEM_QTY, ICM_PPR_ITEM_PRICE, ICM_PPR_ITEM_STD_PRICE"
                    + ", BGM_EXP_SEQ, BGM_SOURCE_SEQ, ICM_RES_ITEM_SEQ, ICM_PPR_SEQ"
                    + ", ICM_PC_RULE_SEQ, BGM_PROJ_EXP_SEQ, BGM_PROJ_OPER_EXP_SEQ"
                    + ", CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@descr"
                    + ", @qty"
                    + ", @price"
                    + ", @std_price"
                    + ", @exp_seq"
                    + ", @source_seq"
                    + ", @item_seq"
                    + ", @ppr_seq"
                    + ", @rule_seq"
                    + ", @projExp_seq"
                    + ", @operExp_seq"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(IList<PrePurchaseRequestItemInfo> infoList, int ppr_seq, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(13);

                    dbManagerTrans.AddParameters(0, "@descr", infoList[i].Descr);
                    dbManagerTrans.AddParameters(1, "@qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(2, "@price", infoList[i].Price);
                    dbManagerTrans.AddParameters(3, "@std_price", infoList[i].Std_price);
                    dbManagerTrans.AddParameters(4, "@exp_seq", infoList[i].InfoExpense.Seq);
                    dbManagerTrans.AddParameters(5, "@source_seq", infoList[i].InfoSource.Seq);
                    dbManagerTrans.AddParameters(6, "@item_seq", infoList[i].InfoItem.Seq);
                    dbManagerTrans.AddParameters(7, "@ppr_seq", ppr_seq);
                    dbManagerTrans.AddParameters(8, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(9, "@created_when", infoList[i].InfoRecord.Created_when);
                    dbManagerTrans.AddParameters(10, "@rule_seq", infoList[i].InfoRule.Seq);
                    dbManagerTrans.AddParameters(11, "@projExp_seq", (infoList[i].InfoProjectExpense != null ? infoList[i].InfoProjectExpense.Seq : null));
                    dbManagerTrans.AddParameters(12, "@operExp_seq", (infoList[i].InfoOperationExpense != null ? infoList[i].InfoOperationExpense.Seq : null));

                    strSQL = "INSERT INTO ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " (ICM_PPR_ITEM_DESCR, ICM_PPR_ITEM_QTY, ICM_PPR_ITEM_PRICE, ICM_PPR_ITEM_STD_PRICE"
                    + ", BGM_EXP_SEQ, BGM_SOURCE_SEQ, ICM_RES_ITEM_SEQ, ICM_PPR_SEQ"
                    + ", ICM_PC_RULE_SEQ, BGM_PROJ_EXP_SEQ, BGM_PROJ_OPER_EXP_SEQ"
                    + ", CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@descr"
                    + ", @qty"
                    + ", @price"
                    + ", @std_price"
                    + ", @exp_seq"
                    + ", @source_seq"
                    + ", @item_seq"
                    + ", @ppr_seq"
                    + ", @rule_seq"
                    + ", @projExp_seq"
                    + ", @operExp_seq"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Update(PrePurchaseRequestItemInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@descr", info.Descr);
                dbManager.AddParameters(2, "@qty", info.Qty);
                dbManager.AddParameters(3, "@price", info.Price);
                dbManager.AddParameters(4, "@std_price", info.Std_price);
                dbManager.AddParameters(5, "@exp_seq", info.InfoExpense.Seq);
                dbManager.AddParameters(6, "@source_seq", info.InfoSource.Seq);
                dbManager.AddParameters(7, "@item_seq", info.InfoItem.Seq);
                dbManager.AddParameters(8, "@ppr_seq", info.InfoPpr.Seq);
                dbManager.AddParameters(9, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(10, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " SET ICM_PPR_ITEM_DESCR = @descr"
                    + ", ICM_PPR_ITEM_QTY = @qty"
                    + ", ICM_PPR_ITEM_PRICE = @price"
                    + ", ICM_PPR_ITEM_STD_PRICE = @std_price"
                    + ", ICM_PPR_SEQ = @ppr_seq"
                    + ", BGM_EXP_SEQ = @exp_seq"
                    + ", BGM_SOURCE_SEQ = @source_seq"
                    + ", ICM_RES_ITEM_SEQ = @item_seq"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PPR_ITEM_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(PrePurchaseRequestItemInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PPR_ITEM_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<PrePurchaseRequestItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PPR_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<PrePurchaseRequestItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " WHERE ICM_PPR_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Restore(IList<PrePurchaseRequestItemInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(1, "@restored_by", infoList[0].InfoRecord.Restored_by);
                dbManager.AddParameters(2, "@restored_when", infoList[0].InfoRecord.Restored_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " SET RECORD_STATUS = @record_status"
                    + ", RESTORED_BY = @restored_by"
                    + ", RESTORED_WHEN = @restored_when"
                    + " WHERE ICM_PPR_ITEM_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateAmount(IList<PrePurchaseRequestItemInfo> infoList, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++ )
                {
                    dbManagerTrans.CreateParameters(3);

                    dbManagerTrans.AddParameters(0, "@icm_ppr_item_seq", infoList[i].Seq);
                    dbManagerTrans.AddParameters(1, "@pr_item_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(2, "@pr_item_amount", infoList[i].Price);

                    strSQL = " UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM" 
                            + " SET PR_ITEM_AMOUNT = @pr_item_amount"
                            + ", PR_ITEM_QTY = @pr_item_qty"
                            + " WHERE ICM_PPR_ITEM_SEQ = @icm_ppr_item_seq";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void UpdateAmountForBidder(IList<PrePurchaseRequestItemInfo> infoList, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(3);

                    dbManagerTrans.AddParameters(0, "@icm_ppr_item_seq", infoList[i].Seq);
                    dbManagerTrans.AddParameters(1, "@pr_item_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(2, "@pr_item_amount", infoList[i].Price);

                    strSQL = " UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                            + " SET PR_ITEM_AMOUNT = @pr_item_amount"
                            + ", PR_ITEM_QTY = @pr_item_qty"
                            + ", BIDDER_AMOUNT = @pr_item_amount"
                            + ", BIDDER_QTY = @pr_item_qty"
                            + " WHERE ICM_PPR_ITEM_SEQ = @icm_ppr_item_seq";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void UpdateAmountForBid(IList<PrePurchaseRequestItemInfo> infoList, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(3);

                    dbManagerTrans.AddParameters(0, "@icm_ppr_item_seq", infoList[i].Seq);
                    dbManagerTrans.AddParameters(1, "@bidder_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(2, "@bidder_amount", infoList[i].Price);

                    strSQL = " UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                            + " SET BIDDER_AMOUNT = @bidder_amount"
                            + ", BIDDER_QTY = @bidder_qty"
                            + " WHERE ICM_PPR_ITEM_SEQ = @icm_ppr_item_seq";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void UpdateAmountForPurchaseOrder(IList<PrePurchaseRequestItemInfo> infoList, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(3);

                    dbManagerTrans.AddParameters(0, "@icm_ppr_item_seq", infoList[i].Seq);
                    dbManagerTrans.AddParameters(1, "@po_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(2, "@po_amount", infoList[i].Price);

                    strSQL = " UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                            + " SET PO_AMOUNT = @po_amount"
                            + ", PO_QTY = @po_qty"
                            + " WHERE ICM_PPR_ITEM_SEQ = @icm_ppr_item_seq";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void UpdateAmountForDeliveryNote(IList<PrePurchaseRequestItemInfo> infoList, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(3);

                    dbManagerTrans.AddParameters(0, "@icm_ppr_item_seq", infoList[i].Seq);
                    dbManagerTrans.AddParameters(1, "@dn_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(2, "@dn_amount", infoList[i].Price);

                    strSQL = " UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                            + " SET DN_AMOUNT = @dn_amount"
                            + ", DN_QTY = @dn_qty"
                            + " WHERE ICM_PPR_ITEM_SEQ = @icm_ppr_item_seq";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public string CheckPurchaseRequestMoney(int pr_seq, IDBManager dbManagerTrans)
        {
            string strSQL = "";
            string returnText = "";
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@pr_seq", pr_seq);

                strSQL = "SELECT F_Auto_Check_Purchase_Request_Money_Qty(@pr_seq)";

                returnText = dbManagerTrans.ExecuteScalar(CommandType.Text, strSQL).ToString();
                return returnText;
            }
            catch
            {
                throw;
            }
        }

        public string CheckPurchaseRequestMoneyForBidder(int pr_seq, IDBManager dbManagerTrans)
        {
            string strSQL = "";
            string returnText = "";
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@pr_seq", pr_seq);

                strSQL = "SELECT F_Auto_Check_Purchase_Request_Money_Qty_Bidder(@pr_seq)";

                returnText = dbManagerTrans.ExecuteScalar(CommandType.Text, strSQL).ToString();
                return returnText;
            }
            catch
            {
                throw;
            }
        }

        public string CheckPurchaseRequestMoneyForBid(int bid_seq, IDBManager dbManagerTrans)
        {
            string strSQL = "";
            string returnText = "";
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@bid_seq", bid_seq);

                strSQL = "SELECT F_Auto_Check_Purchase_Request_Money_Qty_Bid(@bid_seq)";

                returnText = dbManagerTrans.ExecuteScalar(CommandType.Text, strSQL).ToString();
                return returnText;
            }
            catch
            {
                throw;
            }
        }

        public string CheckPurchaseRequestMoneyUrgent(int pr_seq, IDBManager dbManagerTrans)
        {
            string strSQL = "";
            string returnText = "";
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@pr_seq", pr_seq);

                strSQL = "SELECT F_Auto_Check_Purchase_Request_Money(@pr_seq)";

                returnText = dbManagerTrans.ExecuteScalar(CommandType.Text, strSQL).ToString();
                return returnText;
            }
            catch
            {
                throw;
            }
        }

        public string CheckPurchaseRequestMoneyForPurchaseOrder(int po_seq, IDBManager dbManagerTrans)
        {
            string strSQL = "";
            string returnText = "";
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@po_seq", po_seq);

                strSQL = "SELECT F_Auto_Check_Purchase_Request_Money_Qty_Po(@po_seq)";

                DataTable dtb = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    returnText = dtb.Rows[0][0].ToString();
                }
                return returnText;
            }
            catch
            {
                throw;
            }
        }

        public string CheckPurchaseRequestMoneyForDeliveryNote(int dn_seq, IDBManager dbManagerTrans)
        {
            string strSQL = "";
            string returnText = "";
            try
            {
                dbManagerTrans.CreateParameters(1);
                dbManagerTrans.AddParameters(0, "@dn_seq", dn_seq);

                strSQL = "SELECT F_Auto_Check_Purchase_Request_Money_Qty_Dn(@dn_seq)";

                returnText = dbManagerTrans.ExecuteScalar(CommandType.Text, strSQL).ToString();
                return returnText;
            }
            catch
            {
                throw;
            }
        }
    }
}
